package day1217;



import day1216.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class GradeDaoImpl implements GradeDao {

    @Override
    public List<Grade> all() {
        List<Grade>datas=new ArrayList<>();
        Connection conn = DBUtil.getConnection();
        PreparedStatement ps=null;
        ResultSet rs=null;
        try {
            ps=conn.prepareStatement("SELECT * FROM s_grade");
            rs=ps.executeQuery();
            while (rs.next()){
                Grade g=new Grade();
                g.setId(rs.getInt("id"));
                g.setGname(rs.getString("gname"));
                datas.add(g);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(rs,ps,conn);
        }

        return datas;
    }

    @Override
    public Integer add(Grade g) {
        Connection conn = DBUtil.getConnection();
        PreparedStatement ps=null;
        int num=0;
        try {
            String sql="INSERT INTO `s_grade` (`id`, `gname`) VALUES (null ,?);";
            System.out.println(sql);
            ps = conn.prepareStatement(sql);
            ps.setString(1,g.getGname());
            num=ps.executeUpdate();
            //System.out.println(num);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(ps);
            DBUtil.close(conn);
        }

        return num;
    }

    @Override
    public Integer del(int id) {
        Connection conn = DBUtil.getConnection();
        PreparedStatement ps=null;
        int num=0;
        try {
            String sql="DELETE FROM s_grade WHERE id=?;";
            System.out.println(sql);
            ps = conn.prepareStatement(sql);
            ps.setLong(1,id);
            num=ps.executeUpdate();
            //System.out.println(num);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(ps);
            DBUtil.close(conn);
        }

        return num;
    }

    @Override
    public Integer update(Grade g) {
        Connection conn = DBUtil.getConnection();
        PreparedStatement ps=null;
        int num=0;
        try {
            String sql="UPDATE `s_grade` SET `gname` = ? WHERE `id` = ?;";
            System.out.println(sql);
            ps = conn.prepareStatement(sql);
            ps.setString(1,g.getGname());
            ps.setInt(2,g.getId());
            num=ps.executeUpdate();
            //System.out.println(num);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(ps);
            DBUtil.close(conn);
        }

        return num;
    }
}
